package com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.select;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.base.SqlOper;
import org.apache.commons.lang3.StringUtils;

import static com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.base.SqlStringUtils.getSqlBlockAfterIdx;
import static com.zy.explore.db.infrastructure.plugins.multidbsupport.sqlchange.base.SqlStringUtils.isBlank;


public class SqlSelectUtils {
	
	//private static final Pattern SELECT_PATTERN = Pattern.compile("^\\s*(\\b(?i)select\\b)");
	
	/** 查找select需要修改 */
	public static List<SqlOper> find(final String sql){
		
		if(StringUtils.isEmpty(sql)){
			return new ArrayList<>();
		}
		
		List<SelectField> fieldList = new ArrayList<>();
		int start = 0;
		do{
			int selectIdx = getStrStartIdx(sql, "select",start);
			int fromIdx = getStrStartIdx(sql, "from",start);
			if(selectIdx == -1 || fromIdx == -1 || fromIdx < selectIdx){
				//不存在select、from
				break;
			}
			List<SelectField> fieldListTmp = splitSelect(sql,selectIdx+"select".length(),fromIdx);
			fieldList.addAll(fieldListTmp);
			
			start = fromIdx + "from".length();
		}while(true);
		
		return toSqlOpers(fieldList, sql);
	}
	
	/** 获取下一个对应字符位置 */
	private static int getStrStartIdx(final String sql,String key,int start){
		int s= start;
		int idx;
		while(true){
			idx = startIndexOfKey(sql,key, s);
			if(idx == -1){
				break;
			}
			int b = getSqlBlockAfterIdx(sql, start, idx);
			if(b == -1){
				return idx;
			}
			s = Math.max(b, idx);
		}
		return -1;
	}

	/**
	 * 忽略大小写和中间的空格查找关键字（从前往前查找）
	 * @param start
	 * @return
	 */
	private static int startIndexOfKey(final String str,String findKey,int start){
		start = Math.min(start, str.length());
		final int fLen = findKey.length();
		if(fLen < 2){
			return StringUtils.lastIndexOfIgnoreCase(str,findKey, start);
		}
		
		for(int i = start;i < str.length();i++){
			if(Character.toUpperCase(str.charAt(i)) != Character.toUpperCase(findKey.charAt(0))){
				//开始字符不相同
				continue;
			}
			if(i > 0 && Character.isLetterOrDigit(str.charAt(i-1))){
				//匹配字段前面是数字或字母，表示非单词
				continue;
			}
			
			int f = 1;
			int s = i +1;
			boolean flag = true;
			while(f < fLen && s < str.length()){
				char fc = Character.toUpperCase(findKey.charAt(f));
				char sc = Character.toUpperCase(str.charAt(s));
				//字符不相等 或者 都不是空格
				if(fc != sc && !(isBlank(fc) && isBlank(sc))){
					if(isBlank(fc) && isBlank(str.charAt(s+1))){
						//findStr多空格，str单空格
						f ++;
						continue;
					}else if(isBlank(findKey.charAt(f+1)) && isBlank(sc)){
						//findStr单空格，str多空格
						s ++;
						continue;
					}else{
						flag = false;
						break;
					}
				}
				f ++;
				s ++;
			}
			if(flag && f >= fLen){
				if(s < str.length() && (Character.isLetterOrDigit(str.charAt(s)) || str.charAt(s) == '_')){
					//匹配字段前面是数字或字母，表示非单独
					continue;
				}
				return i;
			}
			
		}
		
		return -1;
	}
	
	/** 切分select字段 */
	private static List<SelectField> splitSelect(String sql,int start,int end){
		List<SelectField> fieldList = new ArrayList<>();
		
		int s = start;
		while(s < end){
			char  c = sql.charAt(s);
			if(isBlank(c)){
				s ++;
				continue;
			}
			//逗号位置
			int commaIdx = getCommaIdx(sql, s);
			//逗号已超过结束位置 || 没有逗号
			if(commaIdx > end || commaIdx == -1){
				commaIdx = end;
			}
			
			//去掉末尾的空格
			int e = commaIdx-1;
			while(e > s && isBlank(sql.charAt(e))){
				e--;
			}
			//System.out.println("-- "+sql.substring(s,e+1));
			fieldList.add(new SelectField(sql,s, e+1));
			s = commaIdx + 1;
		}
		
		return fieldList;
	}
	
	/** 获取select中逗号分割位置 */
	private static int getCommaIdx(String str,int start){
		int s= start;
		int idx;
		while(true){
			idx = str.indexOf(",", s);
			if(idx == -1){
				break;
			}
			int b = getSqlBlockAfterIdx(str, start, idx);
			if(b == -1){
				return idx;
			}
			s = Math.max(b, idx);
		}
		return -1;
		
	}

	/** select 中字段 */
	static class SelectField{
		int start;
		int stop;
		//String field;
		public SelectField(String sql,int start, int stop) {
			this.start = start;
			this.stop = stop;
			//field = sql.substring(start,stop);
		}
	} 

	/** name */
	private static final Pattern pattren_field1 = Pattern.compile("^(\\w+)$");
	/** t.name */
	private static final Pattern pattren_field2 = Pattern.compile("^\\w+\\.(\\w+)$");
	/** 已空格+名称结束（NAME as name、NAME name） */
	private static final Pattern pattren_field3 = Pattern.compile("[\\s\\)](\\w+)$");
	
	/** 将切分的字段转换为sql修改 */
	private static List<SqlOper> toSqlOpers(List<SelectField> fieldList,String sql){
		List<SqlOper> sqlOperList = new ArrayList<>();
		for (SelectField selectField : fieldList) {
			int start = selectField.start;
			int stop = selectField.stop;
			String field  = sql.substring(start,stop);
			Matcher matcher = null;
			if((matcher = pattren_field1.matcher(field)).find()
					|| (matcher = pattren_field2.matcher(field)).find()){
				
				sqlOperList.add(SqlOper.newAdd(stop, " \"" + matcher.group(1) + "\""));
			}else if((matcher = pattren_field3.matcher(field)).find()){
				int s = matcher.start(1),e = matcher.end(1);
				//s、e只是截取的偏移量，在 整个sql中需要增加field相对于sql的偏移量start
				sqlOperList.add(SqlOper.newReplace(start + s, start + e, '"' + matcher.group(1) + '"'));
			}
		}
		return sqlOperList;
	}
}

